using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using CoreLab.Oracle;

namespace Common.Utilities.Database
{
    /// <summary>
    /// 
    /// </summary>
    [Serializable]
    public class TableRecord
    {
        private string _TableName;
        private Tuple[] _Fields;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tblKey"></param>
        /// <param name="keyValue"></param>
        public TableRecord(DbTableKey tblKey, string keyValue)
        {
            this._TableName = tblKey.TblName;
            List<string> keyFields = DbUtil.GetTablePrimaryKeys(tblKey.ConnStr, "", this.TableName);
            if(keyFields !=null && keyFields.Count ==1)
            {
                string keyFieldName = keyFields[0];
                TableColumn keyColumn =
                    DbUtil.GetTableColumn(tblKey.DbProvider, tblKey.ConnStr, tblKey.SchemaName, tblKey.TblName,
                                          keyFieldName);
                TableColumn[] cols =
                    DbUtil.GetTableColumns(tblKey.DbProvider, tblKey.ConnStr, tblKey.SchemaName, tblKey.TblName);
                this.Fields=new Tuple[cols.Length];

                IDbConnection conn = null;
                if (tblKey.DbProvider == DataProviderType.Oracle)
                {
                    conn = new OracleConnection(tblKey.ConnStr);
                }
                else
                {
                    conn = new SqlConnection(tblKey.ConnStr);
                }
                try
                {
                    IDbCommand cmd = conn.CreateCommand();
                    string selectSql = "select * from [" +
                                      (string.IsNullOrEmpty(tblKey.SchemaName)
                                           ? tblKey.TblName
                                           : tblKey.SchemaName + "." + tblKey.TblName) +
                                      "] where [" + keyFieldName + "]=";
                    if(tblKey.DbProvider==DataProviderType.Oracle)
                    {
                        selectSql += FieldDataType.ToOracleDataValue(keyColumn.DbType, keyValue);
                    }
                    else
                    {
                        selectSql += FieldDataType.ToMssqlDataValue(keyColumn.DbType, keyValue);
                    }
                    cmd.CommandText = selectSql;
                    IDataReader reader = cmd.ExecuteReader();
                    if(reader.Read())
                    {
                        for(int i=0;i<cols.Length;i++)
                        {
                            Tuple tuple = new Tuple();
                            tuple.DbType = cols[i].DbType;
                            tuple.Name = cols[i].ColumnName;
                            tuple.Value = null;
                            if(reader[cols[i].ColumnName]!=null)
                            {
                                tuple.Value = reader[cols[i].ColumnName].ToString();
                            }
                            this.Fields[i] = tuple;
                        }
                    }
                    reader.Close();
                }
                catch(Exception ex)
                {
                    ExceptionHandler.HandlesException(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                throw new Exception("The table does not have unique field");
            }
            
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="selectStatement"></param>
        public TableRecord(string connStr, string tblName, string selectStatement)
        {
            this._TableName = tblName;
            
            TableColumn[] cols =
                DbUtil.GetTableColumns(connStr, selectStatement);
            this.Fields = new Tuple[cols.Length];

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = selectStatement;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    for (int i = 0; i < cols.Length; i++)
                    {
                        Tuple tuple = new Tuple();
                        tuple.DbType = cols[i].DbType;
                        tuple.Name = cols[i].ColumnName;
                        tuple.Value = null;
                        if (reader[cols[i].ColumnName] != null)
                        {
                            tuple.Value = reader[cols[i].ColumnName].ToString();
                        }
                        this.Fields[i] = tuple;
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tblName"></param>
        /// <param name="spName"></param>
        /// <param name="parameters"></param>
        public TableRecord(string connStr, string tblName, string spName, SqlParameter[] parameters)
        {
            this._TableName = tblName;

            TableColumn[] cols =
                DbUtil.GetTableColumnsFromSelectionStoredProcedure(connStr, spName, parameters);
            this.Fields = new Tuple[cols.Length];

            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = spName;
                cmd.CommandType = CommandType.StoredProcedure;
                if(parameters !=null && parameters.Length>0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    for (int i = 0; i < cols.Length; i++)
                    {
                        Tuple tuple = new Tuple();
                        tuple.DbType = cols[i].DbType;
                        tuple.Name = cols[i].ColumnName;
                        tuple.Value = null;
                        if (reader[cols[i].ColumnName] != null)
                        {
                            tuple.Value = reader[cols[i].ColumnName].ToString();
                        }
                        this.Fields[i] = tuple;
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="xDoc"></param>
        /// <param name="elementName"></param>
        /// <param name="xPath"></param>
        public TableRecord(XmlDocument xDoc, string elementName, string xPath)
        {
            this._TableName = elementName;
            XmlNode xNode = xDoc.SelectSingleNode(xPath);
            this._Fields = null;
            if(xNode!=null && xNode.Attributes.Count>0)
            {
                List<Tuple> tuples = new List<Tuple>();
                foreach(XmlAttribute att in xNode.Attributes)
                {
                    Tuple field = new Tuple();
                    field.Name = att.Name;
                    field.Value = att.Value;
                    field.DbType = SqlDbType.NVarChar;
                    tuples.Add(field);
                }
                this._Fields = tuples.ToArray();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="xDoc"></param>
        /// <param name="elementName"></param>
        /// <param name="elementPath"></param>
        /// <param name="keyAttName"></param>
        /// <param name="keyAttValue"></param>
        public TableRecord(XmlDocument xDoc, string elementName, string elementPath, string keyAttName, string keyAttValue)
        {
            this._TableName = elementName;
            string xPath = elementPath + "[@" + keyAttName + "=" + keyAttValue + "]";
            XmlNode xNode = xDoc.SelectSingleNode(xPath);
            this._Fields = null;
            if (xNode != null && xNode.Attributes.Count > 0)
            {
                List<Tuple> tuples = new List<Tuple>();
                foreach (XmlAttribute att in xNode.Attributes)
                {
                    Tuple field = new Tuple();
                    field.Name = att.Name;
                    field.Value = att.Value;
                    field.DbType = SqlDbType.NVarChar;
                    tuples.Add(field);
                }
                this._Fields = tuples.ToArray();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public string TableName
        {
            get { return _TableName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public Tuple[] Fields
        {
            get { return _Fields; }
            set { _Fields = value; }
        }
    }
}
